package com.al.test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;

import com.al.entity.User;
import com.mchange.v2.c3p0.ComboPooledDataSource;

public class DBUtils {
	private static ComboPooledDataSource dataSource = null;
	
	static {
		dataSource = new ComboPooledDataSource("testc3p0");
	}
	
	public static void main(String[] args) {
		User user = findById();
		System.out.println(user);
		User user2 = findByDBUtils(23);
		System.out.println(user2);
	}
	
	public static User findByDBUtils(Integer id) {
		//用DBUtils只需要定义一个Connection
		Connection connection = null;
		User user = null;
		try {
			connection = dataSource.getConnection();
			String sql = "select * from users where u_id = ?";
			//DBUtils 提供的方法
			QueryRunner queryRunner = new QueryRunner();
			user = queryRunner.query(connection, sql, new BeanHandler<>(User.class), id);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return user;
	}
	
	public static User findById() {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		User user = null;
		try {
			connection = dataSource.getConnection();
			String sql = "select * from users where u_id = 3";
			preparedStatement = connection.prepareStatement(sql);
			resultSet = preparedStatement.executeQuery();
			while(resultSet.next()) {
				Integer id = resultSet.getInt(1);
				String name = resultSet.getString(2);
				Double score = resultSet.getDouble(3);
				user = new User(id, name, score);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				resultSet.close();
				preparedStatement.close();
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return user;
	}
}
